
use "W:\intimate\data\police_DV.dta", clear
g tmp = substr(date_event, -4, .)
destring tmp, replace
replace year_event = tmp
drop tmp
save "W:\intimate\data\police_DV.dta", replace


forvalues i=2006 (1) 2016 {
use "W:\intimate\data\police_DV.dta", clear
rename re_shnro shnro
sort shnro



keep if year_event==`i'
forvalues j=0(1) 5  {
local a= `i'-`j'
local b= `j'
merge m:1 shnro using "D:\ready-made\FLEED_TOTAL\2016\fleed_kokonais_`a'.dta", keepusing(shnro sphnro a18lkm) keep(1 3)
tab _merge
drop _merge 
rename sphnro sphnro`b'
gen spousevictim`b'=ao_shnro==sphnro`b'
tab spousevictim`b'
sort shnro
}

sum spousevictim*
gen spousevictim=ao_shnro==sphnro0 |ao_shnro==sphnro1 |ao_shnro==sphnro2 |ao_shnro==sphnro3 |ao_shnro==sphnro4 |ao_shnro==sphnro5
sum spousevictim*

keep if spousevictim==1
save "W:\intimate\data\police_fleed_`i'.dta", replace

}


forvalues i=2017 (1) 2019 {
use "W:\intimate\data\police_DV.dta", clear
rename re_shnro shnro
sort shnro

keep if year_event==`i'
forvalues j=0(1) 5  {
local a= `i'-`j'
local b= `j'
g vuosi = `a'
merge m:1 shnro vuosi using "W:\intimate\data\folk_spouses.dta",  keep(1 3) 
tab _merge
drop _merge 
drop vuosi

rename sphnro sphnro`b'
gen spousevictim`b'=ao_shnro==sphnro`b'
tab spousevictim`b'
sort shnro
}

sum spousevictim*
gen spousevictim=ao_shnro==sphnro0 |ao_shnro==sphnro1 |ao_shnro==sphnro2 |ao_shnro==sphnro3 |ao_shnro==sphnro4 |ao_shnro==sphnro5
sum spousevictim*
keep if spousevictim==1
save "W:\intimate\data\police_folk_`i'.dta", replace

}

// Append Data
use "W:\intimate\data\police_fleed_2006.dta", clear
forvalues i=2007/2016 {
append using "W:\intimate\data\police_fleed_`i'.dta"
}
forvalues i=2017/2019 {
append using "W:\intimate\data\police_folk_`i'.dta"
}

// Label Data & Rename
* drop unneeded vars 
lab var spousevictim "Spouse Victim"
g spouse_current = (spousevictim0==1 | spousevictim1==1 | spousevictim2==1)
lab var spouse_current "Current Spouse"
g spouse_previous = 0
replace spouse_previous = 1 if spousevictim==1 & spouse_current==0 
lab var spouse_previous "Previous Spouse"

lab var shnro "Perp ID"
lab var ao_shnro "Victim ID"
egen couple_id = group(shnro ao_shnro)
lab var couple_id "Couple ID"
lab var case_id "Police Case ID"

// Drop if not a DV case
drop if spousevictim==0
drop spousevictim

// Duplicates - multiple crime codes. Merge into a single case
bys case_id date_event: gen dup_case = _N
sort case_id year_record
bys case_id date_event: gen dup_case_n = _n
egen crime_id = group(case_id date_event)
xtset crime_id dup_case_n
g crime_second = crime[_n+1] if dup_case_n==1 & dup_case_n < dup_case
lab var crime_second "Second Crime Code"
drop if dup_case_n > 1 // drop duplicate crime in same case now recorded in crime 2
drop dup_* 

// Order 
ren age_plaintiff plaintiff_age
ren age_suspect suspect_age
order case_id couple_id shnro ao_shnro spouse_current spouse_previous year_event 			date_event ///
			year_record crime crimecode6 crime_second municipality ///
			plaintiff_age plaintiff_sex suspect_age suspect_sex, first
keep case_id couple_id shnro ao_shnro spouse_current spouse_previous year_event ///
			date_event ///
			year_record crime crimecode6 crime_second municipality ///
			plaintiff_age plaintiff_sex suspect_age suspect_sex a18lkm


// Save 
save "W:\intimate\data\dvonly_all.dta", replace








